package model;

import java.sql.*;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

/**
 * 继承父类ConnectDB，对blog数据库的article表进行操作
 * 
 * @author faith
 *
 */
public class ControlDB extends ConnectDB {
	Statement sql;
	ResultSet rs;

	/**
	 * 查询数据库中对应 id 的文章
	 * 
	 * @param inputId 需查找文章 id
	 * @return article 以json格式返回文章
	 */
	public JSONObject article(int inputId) {
		JSONObject article = new JSONObject();

		try {
			sql = con.createStatement();
			String sqlStr = "SELECT * FROM article where id='" + inputId + "'";
			rs = sql.executeQuery(sqlStr);

			while (rs.next()) {
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String time = rs.getString("time");
				String category = rs.getString("category");

				article.put("id", id);
				article.put("title", title);
				article.put("content", content);
				article.put("time", time);
				article.put("category",category);

			}

			con.close();
			return article;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 查询article表中的记录数
	 * @return count
	 */
	public int countArticle() {
		try {
			sql = con.createStatement();
			String sqlStr = "SELECT MAX(id) FROM article";
			rs = sql.executeQuery(sqlStr);
			if (rs.next());
			int count = rs.getInt(1);
			con.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * 获取分类
	 * @return
	 */
	public JSONArray getCategory() {
		JSONArray categorys = new JSONArray();
		try {
			sql = con.createStatement();
			String sqlStr = "SELECT DISTINCT category FROM article;";
			rs = sql.executeQuery(sqlStr);
			while(rs.next()) {
				JSONObject category = new JSONObject();
				category.put("category", rs.getString("category"));
				categorys.add(category);
			}
			con.close();
			return categorys;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		
		return null;
	}
	public JSONArray getTitleList() {
		JSONArray lists = new JSONArray();
		try {
			sql = con.createStatement();
			String sqlStr = "SELECT id,title,time FROM article";
			rs = sql.executeQuery(sqlStr);
			while(rs.next()) {
				JSONObject list = new JSONObject();
				list.put("id",rs.getInt("id"));
				list.put("title",rs.getString("title"));
				list.put("time", rs.getString("time"));
				lists.add(list);
			}
			con.close();
			return lists;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		
		return null;
	}
	public JSONArray getTitleList(String inputCategory) {
		JSONArray lists = new JSONArray();
		try {
			sql = con.createStatement();
			String sqlStr = "SELECT id,title,time FROM article WHERE category='"+inputCategory+"'";
			rs = sql.executeQuery(sqlStr);
			while(rs.next()) {
				JSONObject list = new JSONObject();
				list.put("id",rs.getInt("id"));
				list.put("title",rs.getString("title"));
				list.put("time", rs.getString("time"));
				lists.add(list);
			}
			con.close();
			return lists;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		
		return null;
	}
	

//	public static void main(String args[]) {
//		ControlDB query = new ControlDB();
////		query.getCategory();
//		System.out.println(query.getTitleList("运维测试"));
////		System.out.println(query.countArticle());
////		System.out.println("\n"+query.article(10));
////		JSONObject data = query.article("10");
////		JSONObject dataJson =data.getJSONObject("id");
////		if(dataJson != null) {
////			System.out.println("!=null");
////		}else {
////			System.out.println("null");
////		}
////		System.out.println(query.isValidUser("faith","123456"));
////		String param = "标题";
////		System.out.println(query.readContent(param));
////		System.out.println(query.readTime(param));
////		System.out.println(query.readContent(param));
////		query.article();
//	}
}
